﻿Public Class PresentacionDAO
    '@Accion as int,
    '@PresentacionId as int,
    '@PresentacionFecha as datetime,
    '@PresentacionAno as int,
    '@EstadoPresentacionId as int,
    '@MesId as int,
    '@FarmaciaId as int,
    '@ObraSocialXPlanId as int,
    '@EmpleadoAuditorId as int,
    '@EmpleadoRecepcionId as int
    Public Shared Function insertarPresentacion(ByVal PresentacionFecha As DateTime, ByVal PresentacionAno As Integer, ByVal MesId As Integer, ByVal FarmaciaId As Integer, ByVal ObraSocialXPlanId As Integer, ByVal EmpleadoRecepcionId As Integer) As Integer
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_presentacion"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.INSERTAR).DbType = DbType.Int32
                    .AddWithValue("@PresentacionId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@PresentacionFecha", PresentacionFecha).DbType = DbType.DateTime
                    .AddWithValue("@PresentacionAno", PresentacionAno).DbType = DbType.Int32
                    .AddWithValue("@MesId", MesId).DbType = DbType.Int32
                    .AddWithValue("@EstadoPresentacionId", Constante.MENOSUNO).DbType = DbType.Int32
                    '' TODO voy a poner el auditor porque si no me tira error,. hay que verlo bien
                    ''OJO QUE EL EMPLEADO DE RECEPCION ES EL MISMO QUE EL AUDITOR
                    '.AddWithValue("@EmpleadoAuditorId", EmpleadoRecepcionId).DbType = DbType.Int32

                    .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
                    .AddWithValue("@EmpleadoRecepcionId", EmpleadoRecepcionId).DbType = DbType.Int32
                End With
                .Connection = cn
                Return .ExecuteScalar()
            End With

        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return -1
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerPresentaciones(ByVal PresentacionId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_presentacion_obtener", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
            da.Fill(ds, "Presentacion")

            Return ds.Tables("Presentacion")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarPresentacion(ByVal PresentacionId As Integer, ByVal PresentacionFecha As DateTime, ByVal PresentacionAno As Integer, ByVal MesId As Integer, ByVal EstadoPresentacionId As Integer, ByVal FarmaciaId As Integer, ByVal ObraSocialXPlanId As Integer, ByVal EmpleadoRecepcionId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_presentacion"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ACTUALIZAR).DbType = DbType.Int32
                    .AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
                    .AddWithValue("@PresentacionFecha", PresentacionFecha).DbType = DbType.DateTime
                    .AddWithValue("@PresentacionAno", PresentacionAno).DbType = DbType.Int32
                    .AddWithValue("@MesId", MesId).DbType = DbType.Int32
                    .AddWithValue("@EstadoPresentacionId", EstadoPresentacionId).DbType = DbType.Int32
                    .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
                    .AddWithValue("@EmpleadoRecepcionId", EmpleadoRecepcionId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarPresentacion(ByVal PresentacionId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_presentacion"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ELIMINAR).DbType = DbType.Int32
                    .AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
                    .AddWithValue("@PresentacionFecha", Constante.FECHAVACIA).DbType = DbType.DateTime
                    .AddWithValue("@PresentacionAno", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@MesId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@EstadoPresentacionId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@FarmaciaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@ObraSocialXPlanId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@EmpleadoRecepcionId", Constante.MENOSUNO).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerIdPresentacion(ByVal FarmaciaId As Integer, ByVal ObraSocialXPlanId As Integer, ByVal AñoId As Integer, ByVal MesId As Integer) As Integer
        Dim cn As SqlClient.SqlConnection = Nothing
        Dim PresentacionId As Integer = -1
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim consulta As SqlClient.SqlCommand = New SqlClient.SqlCommand()
            With consulta
                .Connection = cn
                .CommandText = "Select PresentacionId From Presentacion Where FarmaciaId = " + FarmaciaId.ToString + " AND ObraSocialXPlanId = " + ObraSocialXPlanId.ToString + " AND PresentacionAno = " + AñoId.ToString + " AND MesId = " + MesId.ToString
                PresentacionId = .ExecuteScalar()
            End With

            Return PresentacionId
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    'Devuelve la cantidad de presentaciones pertenecientes a la obraSocialXPlan, año y mes que vienen por parámetro
    'y cuyo estado es pendiente de auditoría
    Public Shared Function obtenerCantidadPresentaciones(ByVal ObraSocialXPlanId As Integer, ByVal AñoId As Integer, ByVal MesId As Integer) As Integer
        Dim cn As SqlClient.SqlConnection = Nothing
        Dim Cantidad As Integer = -1
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim consulta As SqlClient.SqlCommand = New SqlClient.SqlCommand()
            With consulta
                .Connection = cn
                .CommandText = "Select COUNT(*) From Presentacion Where ObraSocialXPlanId = " + ObraSocialXPlanId.ToString + " AND PresentacionAno = " + AñoId.ToString + " AND MesId = " + MesId.ToString + " AND EstadoPresentacionId = 1"
                Cantidad = .ExecuteScalar()
            End With

            Return Cantidad
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerPresentacionConJoin() As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_presentacion_obtenerXFarmacia", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.Fill(ds, "Presentaciones")

            Return ds.Tables("Presentaciones")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarPresentacion(ByVal PresentacionId As Integer, ByVal EmpleadoAuditorId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_presentacion_empladoAuditor"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
                    .AddWithValue("@EmpleadoAuditorId", EmpleadoAuditorId).DbType = DbType.Int32

                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function


    Public Shared Function obtenerPresentacionesAPagar(ByVal FarmaciaId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim estadoCobrada As Integer = 4
            Dim da As New SqlClient.SqlDataAdapter("usp_presentacion_obtenerPresentacionesAPagar", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@FarmaciaId", FarmaciaId)
            da.Fill(ds, "Presentaciones")

            Return ds.Tables("Presentaciones")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function



End Class
